const express = require("express");
const router = express.Router();
var mysql = require("mysql");
const env = {
  host: "111.231.22.97",
  user: "wangs-web",
  password: "HsTjw82zF7f6fjWY",
  database: "wangs-web", //数据库名字
  multipleStatements: true //两条sql 同时查
};

//新增id
const indexs = () => {
  let str = "";
  for (let i = 0; i < 10; i++) {
    let ind = Math.ceil(Math.random() * 10);
    str += ind + 1;
  }
  return str;
};

//新增需求

//金额相加
const addPrice = (req, res) => {
  const { projectCode, price } = req.body;
  let prices = Number(price);
  let sql = "select price from projectlist where projectCode = ?";
  let pools = mysql.createPool(env);
  pools.query(sql, [projectCode], (err, result) => {
    if (err) {
      res.send({
        code: 500,
        msg: "error",
      });
      console.log(err + "error");
    } else {
      let sum = Number(result[0]["price"]) + prices;
      let sqls = "update projectlist set price = ? where projectCode = ?";
      pools.query(sqls, [sum, projectCode], (err, resk) => {
        if (err) {
          res.send({
            code: 500,
            msg: "error",
          });
          console.log(err + "error");
        } else {
          if (resk.affectedRows >= 1) {
            res.send({
              code: 200,
              msg: "success",
            });
          } else {
            res.send({
              code: 500,
              msg: "error",
            });
          }
        }
      });
    }
  });
};

const postAddOthers = (req, res) => {
  const { projectCode, projectRequire, price, date } = req.body;
  const queryArr = [projectCode, indexs(), projectRequire, price, date, "0"];
  let sql = "insert into projectrequire values(?,?,?,?,?,?)";
  let pools = mysql.createPool(env);
  pools.query(sql, queryArr, (err, result) => {
    if (err) {
      console.log(err, "error");
      res.status(500);
    } else {
      if (result.affectedRows >= 1) {
        addPrice(req, res);
      } else {
        res.send({
          code: 500,
          msg: "新增失败",
        });
      }
    }
  });
};
router.post("/postAddOthers", (req, res) => {
  postAddOthers(req, res);
});

//获取需求
const getAddOthers = (req, res) => {
  let sql = "select * from projectrequire where projectCode = ?";
  let queryArr = [req.body.projectCode];
  let pools = mysql.createPool(env);
  pools.query(sql, queryArr, (err, result) => {
    if (err) {
      res.status(500), console.log(err + "error");
    } else {
      res.send({
        code: 200,
        msg: "success",
        data: {
          data: result,
        },
      });
    }
  });
};
router.post("/getAddOthers", (req, res) => {
  getAddOthers(req, res);
});

//删除+减去金额

const setNums = (req, res) => {
  return new Promise((resolve, reject) => {
    let sql =
      "select projectlist.price from projectlist  inner join projectrequire on projectlist.projectCode = projectrequire.projectCode where projectlist.projectCode = ?";
    let queryArr = [req.body.projectCode];
    let pools = mysql.createPool(env);
    pools.query(sql, queryArr, (err, result) => {
      if (err) {
        res.status(500);
        console.log(err, "error");
      } else {
        let totals = Number(result[0]["price"]);
        console.log(totals, "111");
        let requireNums = Number(req.body.price);
        let numbers = totals - requireNums;
        let sqls = "update projectlist set price = ? where projectCode = ?";
        pools.query(sqls, [numbers, req.body.projectCode], (err, resk) => {
          if (err) {
            resolve(false);
            console.log(err, "error");
          } else {
            if (resk.affectedRows >= 1) {
              resolve(true);
            } else {
              resolve(false);
            }
          }
        });
      }
    });
  });
};

const deleteId = (req, res) => {
  return new Promise((resolve, reject) => {
    let sql = "delete from projectrequire where id = ?";
    let pools = mysql.createPool(env);
    pools.query(sql, [req.body.id], (err, result) => {
      if (err) {
        resolve(false);
        console.log(err, "error");
      } else {
        if (result.affectedRows >= 1) {
          resolve(true);
        } else {
          resolve(false);
        }
      }
    });
  });
};

const deleteAddOthers = async (req, res) => {
  let deterNume = await setNums(req, res);
  let deleteIds = await deleteId(req, res);
  if (deterNume == true && deleteIds == true) {
    res.send({
      code: 200,
      msg: "success",
    });
  } else {
    res.send({
      code: 500,
      msg: "error",
    });
  }
};

router.post("/deleteAddOthers", (req, res) => {
  deleteAddOthers(req, res);
});

/* 首页echarts图分月份分年查阅 */

const twoDArray = (data) => {
  return new Promise((resolve, reject) => {
    let fit = []; //判断
    let resArr = [];
    data.forEach((els) => {
      if (!fit.includes(JSON.stringify(els))) {
        fit.push(JSON.stringify(els));
        resArr.push(els);
      }
    });
    resolve(resArr);
  });
};

const getProjectEcharts = (req, res) => {
  let pools = mysql.createPool(env);
  let resArr = [];
  const year = new Date(req.body.date).getFullYear();
  const month = new Date(req.body.date).getMonth() + 1;
  console.log(year, month);
  let sql = "select * from projectlist where projectStatus = ?";
  pools.query(sql, ["1"], async (err, result) => {
    if (err) {
      res.status(500);
      console.log(err, "error");
    } else {
      let arrs = []; //项目名称
      let arres = []; //项目金额
      result.forEach((el) => {
        let year1 = new Date(el.startTime).getFullYear();
        let month1 = new Date(el.startTime).getMonth() + 1;
        console.log(year1, month1);
        console.log(year, month);
        if (year === year1 && month === month1) {
          arrs.push(el.projectName);
          arres.push(el.price);
          resArr.push(arrs, arres);
        }
      });
      //二维数组去重
      res.send({
        code: 200,
        msg: "success",
        data: {
          data: await twoDArray(resArr),
        },
      });
    }
  });
};
router.post("/showProjectEcharts", (req, res) => {
  getProjectEcharts(req, res);
});


/* 草稿新增 */
const indexes = () => {
  let str = "";
  for (let i = 0; i < 10; i++) {
    let ind = Math.ceil(Math.random() * 10);
    str += ind + 1;
  }
  return 'ws' + str;
};

//0表示草稿状态 1表示已加入projectlist
//草稿必须带上member成员字段
const postDrafProject = (req, res) => {
  let pools = mysql.createPool(env)
  let { projectName, projectPerson, startTime, endTime, price, projectURL, member } = req.body
  console.log(member,'测试')
  let queryArr = [indexes(), projectName, projectPerson, startTime, endTime, price, projectURL, member, '0']
  let sql = 'insert into projectdraf values (?,?,?,?,?,?,?,?,?)'
  pools.query(sql, queryArr, (err, resk) => {
    try {
      if (resk.affectedRows >= 1) {
        res.send({
          code: 200,
          msg: 'success'
        })
      }
      else {
        res.send({
          code: 500,
          msg: 'error'
        })
      }
    } catch {
      res.status(500)
      console.log(err, 'error')
    }
  })
}
router.post('/postDrafProject', (req, res) => {
  postDrafProject(req, res)
})

/* 草稿获取 */
const filters = (e) => {
  let objs = {};
  for (let keys in e) {
    if (e[keys] != "" && e[keys] != undefined && e[keys] != null) {
      objs[keys] = e[keys];
    }
  }
  return Object.keys(objs);
};
const getDrafProject = (req, res) => {
  let arrs = filters(req.query)
  let pools = mysql.createPool(env)
  let start = (Number(req.query.pageNo) - 1) * Number(req.query.pageSize)
  let end = Number(req.query.pageNo) * Number(req.query.pageSize)
  if (arrs.length == 3) {
    let sql = 'select * from projectdraf where status = ? and projectPerson = ? '
    let queryArr = ['0', req.query.person]
    pools.query(sql, queryArr, (err, resp) => {
      try {
        let data = resp.reverse().slice(start, end)
        res.send({
          code: 200,
          msg: 'success',
          data: {
            data: data,
            total: resp.length
          }
        })
        console.log(data,'data')

      } catch {
        res.status(500)
        console.log(err, 'error')
      }
    })
  }
  else {
    let sql = 'select * from projectdraf where status = ? and projectPerson = ? and '
    let target = arrs.filter(res => { return res != 'pageNo' && res != 'pageSize' && res!='person'})
    let queryArr = ['0',req.query.person]
    target.forEach((els, index) => {
      if (index != target.length - 1) {
        sql += `${els} = ? and `
        queryArr.push(req.query[`${els}`])
      }
      else {
        sql += `${els} = ? `
        queryArr.push(req.query[`${els}`])
      }
    })
    console.log(sql)
    pools.query(sql, queryArr, (err, reso) => {
      try {
        let data = reso.reverse().slice(start, end)
        res.send({
          code: 200,
          msg: 'success',
          data: {
            data: data,
            total: reso.length
          }
        })

      } catch {
        res.status(500)
        console.log(err, 'error')
      }
    })
  }
}
router.get('/getDrafProject', (req, res) => {
  getDrafProject(req, res)
})

//草稿删除
const deleteDrafProject = (req, res) => {
  //根据projectCode删除
  let pools = mysql.createPool(env)
  let { projectCode } = req.query
  let sql = 'delete from projectdraf where projectCode = ? '
  pools.query(sql, [projectCode], (err, resp) => {
    try {
      if (resp.affectedRows >= 1) {
        res.send({
          code: 200,
          msg: 'success'
        })
      }
      else {
        res.send({
          code: 500,
          msg: 'error'
        })
      }
    } catch {
      res.status(500)
      console.log(err, 'error')
    }
  })
}
router.get('/deleteDrafProject', (req, res) => {
  deleteDrafProject(req, res)
})

//草稿转projectlist


const drafChangeToList = (req, res) => {
  //查询
  let pools = mysql.createPool(env)
  let { projectCode } = req.body
  pools.query('select * from projectdraf where projectCode = ? ', [projectCode], (err, resl) => {
    try {
      let { projectCode, projectName, projectPerson, startTime, endTime, price, projectURL, member } = resl[0]
      let queryArr = [projectCode, projectName, projectPerson, startTime, endTime, price, projectURL, '0', projectCode, member, projectCode]
      pools.query('insert into projectlist values (?,?,?,?,?,?,?,?);insert into projectdetail values (?,?);delete from projectdraf where projectCode = ? ', queryArr, (err, resk) => {
        try {
          if (resk.every(el => { return el.affectedRows >= 1 })) {
            res.send({
              code: 200,
              msg: 'success'
            })
          }
          else {
            res.send({
              code: 500,
              msg: 'error'
            })
          }
        }
        catch {
          res.status(500)
          console.log(err)
        }
      })
    } catch {
      res.status(500)
      console.log(err, 'error')
    }
  })
}
router.post('/drafChangeToList', (req, res) => {
  drafChangeToList(req, res)
})



//获取已创建、草稿的总数
const getAboutProject = (req, res) => {
  console.log(req.query.person)
  let sql = 'select COUNT(*) from projectlist;select COUNT(*) from projectdraf where projectPerson = ?'
  let pools = mysql.createPool(env)
  pools.query(sql, [req.query.person], (err, result) => {
    if (err) {
      res.status(500)
    }
    console.log(result)
    let obj = {
      already: result[0][0]['COUNT(*)'],
      draf: result[1][0]['COUNT(*)']
    }
    console.log(obj)
    res.send({
      code: 200,
      msg: 'success',
      data: {
        data: obj
      }
    })
  })
}
router.get('/getAboutProject', (req, res) => {
  getAboutProject(req, res)
})



//编辑
const editDrafList = (req, res) => {
  let { projectCode, projectName, startTime, endTime, price, projectURL, member } = req.body
  let pools = mysql.createPool(env)
  let sql = 'update projectdraf set projectName = ?,startTime = ?,endTime=?,price=?,projectURL=?,member=? where projectCode=?'
  let queryArr = [projectName, startTime, endTime, price, projectURL, member, projectCode]
  pools.query(sql, queryArr, (err, result) => {
    try {
      if (result.affectedRows >= 0) {
        res.send({
          code: 200,
          msg: 'success'
        })
      }
    } catch {
      res.status(err)
      console.log(err)
    }
  })
}
router.post('/editDrafList', (req, res) => {
  editDrafList(req, res)
})



module.exports = router;
